<?php
/**
 * Created by PhpStorm.
 * User: Leslie
 * Date: 2016/6/23
 * Time: 11:02
 */

namespace app\models;


use app\librarys\DbCacheUtility;
use app\librarys\Inteface;
use app\librarys\Tools;

class User
{
    const CacheKey_SmsVerificationCode = 'sms_verification_code:';
    const CacheKey_UserTicket = 'user_ticket:';
    const UserTicket_ExpiresIn = 3600;  // user ticket过期时间60分钟

    /**
     * 用户详情
     * @param $id
     * @return array|false
     */
    public static function getUser($id)
    {
        $sql = "SELECT * FROM t_user WHERE id=:id";
        $cmd = \Yii::$app->db->createCommand($sql);

        $cmd->bindValue(':id', $id);
        $result = $cmd->queryOne();
        if ($result == false) {
            return null;
        }
        return $result;

    }

    /**
     * 根据微信openid来获取此用户在所有机构中的信息
     * @param $wxOpenid
     * @return array|false|null
     */
    public static function getUserListByWxOpenid($wxOpenid)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT * FROM t_user WHERE wx_openid=:wxOpenid AND is_enabled=1');

        $cmd->bindValue(':wxOpenid', $wxOpenid);
        $result = DbCacheUtility::queryAll($cmd, 10);
        return $result;
    }

    /**
     * 根据微信openid来获取此openid用户之前绑定过的手机号
     * @param $wxOpenid
     * @return mixed|null
     */
    public static function getUserPhoneByWxOpenid($wxOpenid)
    {
        $cmd = \Yii::$app->db->createCommand("SELECT phone FROM t_user WHERE wx_openid=:wxOpenid AND is_enabled=1 AND phone IS NOT NULL AND phone != '' LIMIT 1");

        $cmd->bindValue(':wxOpenid', $wxOpenid);
        $phone = DbCacheUtility::queryScalar($cmd, 10);
        if ($phone == false) {
            return null;
        } else {
            return $phone;
        }
    }

    /**
     * 根据微信openid来获取此用户所属哪些机构,以及在各个机构中的剩余积分
     * @param $wxOpenid
     * @return array
     */
    public static function getInstitutionListByWxOpenid($wxOpenid)
    {
        $cmd = \Yii::$app->db->createCommand("
        SELECT
            CASE WHEN t2.site_name IS NULL OR t2.site_name='' THEN CONCAT(t1.`name`, '积分商城') ELSE t2.site_name END AS site_name,
            t0.integral,
            t0.institution_code,
            t1.integral_unit
        FROM t_user t0
        LEFT JOIN t_institution t1 ON t1.`code`=t0.institution_code
        LEFT JOIN t_institution_site_config t2 ON t2.institution_code=t1.`code`
        WHERE t0.wx_openid=:wxOpenid AND t0.is_enabled=1");

        $cmd->bindValue(':wxOpenid', $wxOpenid);
        $items = $cmd->queryAll();
        return $items;
    }
    public static function getInstitutionListByPhone($phone)
    {
        $cmd = \Yii::$app->db->createCommand("
        SELECT
            CASE WHEN t2.site_name IS NULL OR t2.site_name='' THEN CONCAT(t1.`name`, '积分商城') ELSE t2.site_name END AS site_name,
            t0.integral,
            t0.institution_code,
            t1.integral_unit
        FROM t_user t0
        LEFT JOIN t_institution t1 ON t1.`code`=t0.institution_code
        LEFT JOIN t_institution_site_config t2 ON t2.institution_code=t1.`code`
        WHERE t0.phone=:phone AND t0.is_enabled=1");

        $cmd->bindValue(':phone', $phone);
        $items = $cmd->queryAll();
        return $items;
    }
    /**
     * 机构会员列表
     * @param $start
     * @param $pagesize
     * @param $status
     * @param $filter
     * @param $institutionId
     * @return array
     */
    public static function getUserList($start, $pagesize, $status, $filter, $institutionId, $department_id = '')
    {
        $filterSQL = 't1.institution_code= :institutionId';

        if ($filter != '0') {
            $filterSQL = $filterSQL . ' and ((t1.name like :filter) or (t1.phone like :filter))';
        }

        if ($status != '99') {
            $filterSQL = $filterSQL . ' and (t1.is_enabled= :status)';
        }

        if (!empty($department_id)) {
            $department = Department::getDepartment($department_id);
            if ($department <> null) {
                $code_tree = $department['code_tree'];
                $filterSQL = $filterSQL . ' and t3.code_tree LIKE :code_tree ';
            }
        }

        if ($pagesize == '0') {
            $sql = "SELECT u.*,du.department_name FROM (SELECT DISTINCT t1.* FROM v_institution_user t1
                        LEFT JOIN t_department_user t2 ON t2.user_id = t1.id
                        LEFT JOIN (select * from t_department where  is_delete=0) t3 ON t3.id = t2.department_id
                        WHERE  $filterSQL ) AS u LEFT JOIN v_department_user du on u.id=du.user_id ";

        } else {
            $sql = "SELECT u.*,du.department_id,du.department_name FROM (SELECT DISTINCT t1.* FROM v_institution_user t1
                        LEFT JOIN t_department_user t2 ON t2.user_id = t1.id
                        LEFT JOIN (select * from t_department where  is_delete=0) t3 ON t3.id = t2.department_id
                        WHERE  $filterSQL) AS u LEFT JOIN v_department_user du on u.id=du.user_id
                        LIMIT $start,$pagesize";
        }


        /*        if ($pagesize == '0') {
                    $sql = "SELECT * FROM v_institution_user t1 WHERE $filterSQL";
                } else {
                    if (!$isDepartmentAdmin) {
                        $sql = "SELECT * FROM v_institution_user t1 WHERE $filterSQL LIMIT $start,$pagesize";
                    } else {
                        if (!empty($department_id)) {
                            $filterSQL = $filterSQL . ' and (t3.id = :department_id)';
                        }
                        $sql = "SELECT DISTINCT t1.* FROM v_institution_user t1
                                INNER JOIN t_department_user t2 ON t2.user_id = t1.id
                                INNER JOIN t_department t3 ON t3.id = t2.department_id
                                WHERE t3.code_tree LIKE :code_tree AND $filterSQL";
                    }
                }*/

        $cmd = \Yii::$app->db->createCommand($sql);

        $cmd->bindValue(':institutionId', $institutionId);

        /*        if ($isDepartmentAdmin) {
                    $cmd->bindValue(':code_tree', $code_tree . '%');
                    if (!empty($department_id)) {
                        $cmd->bindValue(':department_id', $department_id);
                    }
                }*/
        if (!empty($code_tree)) {
            $cmd->bindValue(':code_tree', '%' . $code_tree . '%');
        }


        if ($filter != '0') {
            $filter = '%' . $filter . '%';
            $cmd->bindValue(':filter', $filter);
        }
        if ($status != '99') {
            $cmd->bindValue(':status', (int)$status);
        }

        $result = $cmd->queryAll();

        return $result;

    }

    /**
     * 机构会员总数
     * @param $status
     * @param $filter
     * @param $institutionId
     * @return mixed
     */
    public static function getUserListTotal($status, $filter, $institutionId, $department_id = '')
    {
        $filterSQL = 't1.institution_code= :institutionId';

        if ($filter != '0') {
            $filterSQL = $filterSQL . ' and ((t1.name like :filter) or (t1.phone like :filter))';
        }

        if ($status != '99') {
            $filterSQL = $filterSQL . ' and (t1.is_enabled= :status)';
        }

        if (!empty($department_id)) {
            $department = Department::getDepartment($department_id);
            if (!($department == ''|| $department == null)) {
                $code_tree = $department['code_tree'];
                $filterSQL = $filterSQL . ' and t3.code_tree LIKE :code_tree ';
            }
        }

        $sql = "SELECT count(*) AS totalnum,sum(u.integral) as totalintegral FROM (SELECT DISTINCT t1.* FROM v_institution_user t1
                        LEFT JOIN t_department_user t2 ON  t1.id=t2.user_id
                        LEFT JOIN (select * from t_department where  is_delete=0) t3 ON t2.department_id=t3.id
                        WHERE  $filterSQL ) AS u";

        /*if (!$isDepartmentAdmin) {
            $sql = "SELECT count(*) AS totalnum FROM v_institution_user t1 WHERE $filterSQL";
        } else {
            if (!empty($department_id)) {
                $filterSQL = $filterSQL . ' and (t3.id = :department_id)';
            }
            $sql = "
                    SELECT count(*) AS totalnum FROM (
                    SELECT DISTINCT t1.* FROM v_institution_user t1
                        INNER JOIN t_department_user t2 ON t2.user_id = t1.id
                        INNER JOIN t_department t3 ON  t3.code_tree LIKE :code_tree
                    WHERE $filterSQL) as t";
        }*/

        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institutionId', $institutionId);
        /*        if ($isDepartmentAdmin) {
                    $cmd->bindValue(':code_tree', $code_tree . '%');
                    if (!empty($department_id)) {
                        $cmd->bindValue(':department_id', $department_id);
                    }
                }*/
        if (!empty($code_tree)) {
            $cmd->bindValue(':code_tree', '%' . $code_tree . '%');
        }
        if ($filter != '0') {
            $filter = '%' . $filter . '%';
            $cmd->bindValue(':filter', $filter);
        }
        if ($status != '99') {
            $cmd->bindValue(':status', (int)$status);
        }

        $result = $cmd->queryOne();

        return $result;
    }

    /**
     * 新增修改用户
     * @param $record (id,code,name,institution_code,phone,isEnabled,remark)
     * @return mixed
     */
    public static function addUser($record)
    {
        $sql = "call p_save_user(:in_id,:in_code,:in_name,:in_institution_code,:in_phone,:in_isEnabled,:in_remark,:in_wx_openid,:in_header_url,@userId)";

        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':in_id', $record['id']);
        $cmd->bindValue(':in_code', $record['code']);
        $cmd->bindValue(':in_name', $record['name']);
        $cmd->bindValue(':in_institution_code', $record['institution_code']);
        $cmd->bindValue(':in_phone', $record['phone']);
        $cmd->bindValue(':in_isEnabled', $record['isEnabled']);
        $cmd->bindValue(':in_remark', $record['remark']);
        $cmd->bindValue(':in_wx_openid', $record['wx_openid']);
        $cmd->bindValue(':in_header_url', $record['header_url']);

        $result['result'] = $cmd->queryScalar();

        $cmd = \Yii::$app->db->createCommand('SELECT @userId');
        $result['userId'] = $cmd->queryScalar();

        //添加用户到部门
        if (isset($record['is_del_department']) && $record['is_del_department'] == '1') {
            //删除用户与部门的关系
            //$re[0] = $result['userId'];
            Department::delUserFromDepartments($record['del_department'], $result['userId']);
        }
        if (isset($record['department_id']) && !($record['department_id'] == null || $record['department_id'] == '')) {
            //新增用户到某部门
            $departments = explode(",", $record['department_id']);
            foreach ($departments as $u) {
                Department::addUserToDepartment($u, $result['userId']);
            }
            $result['result'] = '0';
        }


        return $result;
    }

    /**
     * 变更用户状态
     * @param $id
     * @param $isEnabled
     * @return \Exception|int
     * @throws \yii\db\Exception
     */
    public static function setUserStatus($id, $isEnabled)
    {
        //$sql = "call p_set_user_status(:id,:isEnabled)";
        $sql = "UPDATE t_user SET is_enabled=" . $isEnabled . "  WHERE id in (" . $id . ")";
        $cmd = \Yii::$app->db->createCommand($sql);
        /*$cmd->bindValue(":id", $id);
        $cmd->bindValue(":isEnabled", $isEnabled);
        $result = $cmd->queryScalar();*/
        $dbcon = \Yii::$app->db;
        $transaction1 = $dbcon->beginTransaction();
        try {
            $cmd = $dbcon->createCommand($sql);
            $cmd->execute();
            $transaction1->commit();
            return 0;
        } catch (\Exception $e) {
            $transaction1->rollBack();
            return $e;
        }
    }

    /**
     * 变更用户名及手机号码
     * @param $name
     * @param $phone
     * @return \Exception|int
     * @throws \yii\db\Exception
     */
    public static function setUserName($id, $name, $phone)
    {
        $sql = "UPDATE t_user SET name=:name ,phone=:phone WHERE id =:id ";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':name', $name);
        $cmd->bindValue(':phone', $phone);
        $cmd->bindValue(':id', $id);
        $rt = $cmd->execute();
        if ($rt > 0) {
            return 0;
        } else {
            return 1;
        }

    }

    /**
     * 导入新增修改用户及积分
     * @param $record (id,name,institution_code,phone,isEnabled,remark,type,integral,send_record_id,reason) id为数据库中的code字段
     * @return mixed
     */
    public static function setUserIntegral($record)
    {
        $sql = "call p_set_userIntegral(:in_code,:in_name,:in_institution_code,:in_phone,:in_isEnabled,:in_remark,:in_type,:in_integral,:in_walfare_id,:in_reason,@userId)";

        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':in_code', $record['id']);
        $cmd->bindValue(':in_name', $record['name']);
        $cmd->bindValue(':in_institution_code', $record['institution_code']);
        $cmd->bindValue(':in_phone', $record['phone']);
        $cmd->bindValue(':in_isEnabled', $record['isEnabled']);
        $cmd->bindValue(':in_remark', $record['remark']);
        $cmd->bindValue(':in_type', $record['type']);
        $cmd->bindValue(':in_integral', $record['integral']);
        $cmd->bindValue(':in_walfare_id', $record['send_record_id']);
        $cmd->bindValue(':in_reason', $record['reason']);

        $result['result'] = $cmd->queryScalar();

        $cmd = \Yii::$app->db->createCommand('SELECT @userId');
        $result['userId'] = $cmd->queryScalar();
        //添加用户到部门
        if (!($record['department_id'] == null || $record['department_id'] == '')) {
            Department::addUserToDepartment($record['department_id'], $result['userId']);
        }
        return $result;
    }

    /**
     * 获取用户详情
     * @param $userId
     * @return array|false|null
     */
    public static function getUserDetail($userId)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT * FROM (SELECT * FROM v_institution_user  WHERE id=:user_id) AS t1   LEFT JOIN (SELECT * FROM v_department_user WHERE user_id=:user_id ) AS t2 ON  t1.id=t2.user_id ');
        $cmd->bindValue(':user_id', $userId);

        $item = $cmd->queryOne();
        if ($item != false) {
            return $item;
        } else {
            return null;
        }
    }

    /**
     * 给用户发送短信验证码
     * @param $institutionCode
     * @param $phone
     * @return bool
     * @throws \Exception
     */
    public static function sendSmsVerificationCode($phone)
    {
        $timeValidity = \Yii::$app->params['sms.TimeValidity'];
        $verificationCode = User::genSmsVerificationCode($phone, $timeValidity);

        // 发送短信验证码的模板id为57758
        $sendStatus = Inteface::sendSms($phone, 57758, [$verificationCode, $timeValidity]);
        if ($sendStatus->status == 0) {
            return true;
        } else {
            throw new \Exception($sendStatus->message);
        }
//        return true;
    }

    /**
     * 发送短信
     * @param $templet_id
     * @param $phone
     * @param $array
     * @return bool
     * @throws \Exception
     */
    public static function sendSms($templet_id, $phone, $array)
    {
        $sendStatus = Inteface::sendSms($phone, $templet_id, $array);
        if ($sendStatus->status == 0) {
            return true;
        } else {
            throw new \Exception($sendStatus->message);
        }
    }

    /**
     * 生成短信验证码
     * @param $phone
     * @param $timeValidity
     * @return int|string
     */
    public static function genSmsVerificationCode($phone, $timeValidity)
    {
        $verificationCode = rand(0, 999999);
        $verificationCode = str_pad($verificationCode, 6, '0', STR_PAD_LEFT);
        $cacheKey = self::CacheKey_SmsVerificationCode . $phone;
        \Yii::$app->cache->set($cacheKey, $verificationCode, $timeValidity * 60);

        return $verificationCode;
    }

    /**
     * 验证短信验证码
     * @param $phone
     * @param $verificationCode
     * @return bool
     */
    public static function authSmsVerificationCode($phone, $verificationCode)
    {
        // TODO 调试使用
        if ($phone == '13637993158' && $verificationCode == '123456') {
            return true;
        }

        if (empty($verificationCode)) {
            return false;
        }
        $cacheKey = self::CacheKey_SmsVerificationCode . $phone;
        $cacheVerificationCode = \Yii::$app->cache->get($cacheKey);
        if ($verificationCode == $cacheVerificationCode) {
            \Yii::$app->cache->delete($cacheKey);
            return true;
        } else {
            return false;
        }
    }

    /**
     * 通过用户手机号码,返回某机构此手机号码用户信息
     * @param $institutionCode
     * @param $phone
     * @return array|false|null
     */
    public static function getUserByPhone($institutionCode, $phone)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT * FROM t_user WHERE phone=:phone AND institution_code=:institution_code');
        $cmd->bindValue(':phone', $phone);
        $cmd->bindValue(':institution_code', $institutionCode);

        $item = $cmd->queryOne();
        if ($item != false) {
            return $item;
        } else {
            return null;
        }
    }

    /**
     * 通过用户微信openid,返回某机构此openid用户信息
     * @param $institutionCode
     * @param $wxOpenid
     * @return array|false|null
     */
    public static function getUserByWeixinOpenid($institutionCode, $wxOpenid)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT * FROM t_user WHERE wx_openid=:wxOpenid AND institution_code=:institution_code');
        $cmd->bindValue(':wxOpenid', $wxOpenid);
        $cmd->bindValue(':institution_code', $institutionCode);

        $item = $cmd->queryOne();
        if ($item != false) {
            return $item;
        } else {
            return null;
        }
    }

    /**
     * 通过用户微信openid,返回某机构此openid用户信息
     * @param $institutionCode
     * @param $wxOpenid
     * @return array|false|null
     */
    public static function getEnabledUserByWeixinOpenid($institutionCode, $wxOpenid)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT * FROM t_user WHERE wx_openid=:wxOpenid AND institution_code=:institution_code AND is_enabled = 1');
        $cmd->bindValue(':wxOpenid', $wxOpenid);
        $cmd->bindValue(':institution_code', $institutionCode);

        $item = $cmd->queryOne();
        if ($item != false) {
            return $item;
        } else {
            return null;
        }
    }

    /**
     * 通过用户code,返回某机构用户信息
     * @param $institutionCode
     * @param $userCode
     * @return array|false|null
     */
    public static function getUserByCode($institutionCode, $userCode)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT * FROM t_user WHERE code=:user_code AND institution_code=:institution_code');
        $cmd->bindValue(':user_code', $userCode);
        $cmd->bindValue(':institution_code', $institutionCode);

        $item = $cmd->queryOne();
        if ($item != false) {
            return $item;
        } else {
            return null;
        }
    }

    public static function loginWithPhone($institutionCode, $phone, $verificationCode)
    {
        $auth = self::authSmsVerificationCode($phone, $verificationCode);
        //调试用
//        $auth = true && $phone=='13983244887';
        if ($auth == true) {
            $user = User::getUserByPhone($institutionCode, $phone);
            if ($user != null) {
                $userTicket = self::_setUserTicket($user['id']);
                $user['ticket'] = $userTicket;
                return $user;
            }
        }

        return null;
    }

    /**
     * 系统直接获取用户登录信息
     * @param string $institutionCode
     * @param string $userCode
     * @param string $userName
     * @return array|false|null
     */
    public static function autoLogin($institutionCode, $userCode, $userName)
    {
        $userCode = $institutionCode . 'U' . $userCode;  // userCode进入到系统以后,根据规则:{机构编码}U{userCode}生成本系统对于此用户的code
        $user = User::getUserByCode($institutionCode, $userCode);
        $userId = null;
        if ($user == null) {
            $record = [
                'id' => 0,
                'code' => $userCode,
                'name' => $userName,
                'institution_code' => $institutionCode,
                'phone' => null,
                'isEnabled' => 1,
                'remark' => '免登陆用户',
                'wx_openid' => null,
                'header_url' => null
            ];

            $userId = User::addUser($record)['userId'];
        } else if ($user['is_enabled'] == 1) {
            $userId = $user['id'];
            $userName = empty($user['name']) ? $userName : $user['name'];
        }

        if ($userId == null) {
            return null;
        }

        $userTicket = self::_setUserTicket($userId);

        return [
            'name' => $userName,
            'ticket' => $userTicket
        ];
    }

    /**
     * 实时获取用户账户积分
     * @param $userId
     * @return false|int|null|string
     */
    public static function getUserIntegral($userId)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT integral FROM t_user WHERE id=:userId');
        $cmd->bindValue(':userId', $userId);

        $integral = $cmd->queryScalar();
        if ($integral == false) {
            $integral = 0;
        }

        return $integral;
    }

    /**
     * 扣除用户积分
     * @param string $userId
     * @param float $payIntegral
     * @param int $type
     * @param string $remark
     * @return false|int|null|string
     * @throws \yii\db\Exception
     */
    public static function payUserIntegral($userId, $payIntegral, $type, $remark)
    {
        $status = 1;

        $transaction = \Yii::$app->db->beginTransaction();
        try {
            $cmd = \Yii::$app->db->createCommand('CALL p_pay_user_integral(:in_user_id, :in_pay_integral, :in_type, :in_remark)');
            $cmd->bindValue(':in_user_id', $userId);
            $cmd->bindValue(':in_pay_integral', $payIntegral);
            $cmd->bindValue(':in_type', $type);
            $cmd->bindValue(':in_remark', $remark);

            $status = $cmd->queryScalar();//存储过程处理成功后返回0, 积分不足返回10

            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            $status = 1;
        }

        return $status;
    }

    public static function authUserTicket($userTicket)
    {
        $ticketCacheKey = self::CacheKey_UserTicket . $userTicket;
        $userId = \Yii::$app->cache->get($ticketCacheKey);
        if ($userId == false) {
            return null;
        }

        $userIdCacheKey = self::CacheKey_UserTicket . $userId;
        \Yii::$app->cache->set($userIdCacheKey, $ticketCacheKey, self::UserTicket_ExpiresIn);
        \Yii::$app->cache->set($ticketCacheKey, $userId, self::UserTicket_ExpiresIn);

        return $userId;
    }

    private static function _setUserTicket($userId)
    {
        // 设置ticket之前,先将此userId用户以前所用的ticket清除,已保证每个用户同时只有一个ticket有效。
        $userIdCacheKey = self::CacheKey_UserTicket . $userId;
        $ticketCacheKey = \Yii::$app->cache->get($userIdCacheKey);
        if ($ticketCacheKey != false) {
            \Yii::$app->cache->delete($ticketCacheKey);
        }
        \Yii::$app->cache->delete($userIdCacheKey);

        $userTicket = Tools::uuid();
        $ticketCacheKey = self::CacheKey_UserTicket . $userTicket;

        \Yii::$app->cache->set($userIdCacheKey, $ticketCacheKey, self::UserTicket_ExpiresIn);
        \Yii::$app->cache->set($ticketCacheKey, $userId, self::UserTicket_ExpiresIn);

        return $userTicket;
    }

    public static function integralTransfer($userId, $receiveUserId, $integral)
    {
        $status = 1;

        $transaction = \Yii::$app->db->beginTransaction();
        try {
            $cmd = \Yii::$app->db->createCommand('CALL p_integral_transfer(:in_user_id, :in_receive_user_id, :in_integral)');
            $cmd->bindValue(':in_user_id', $userId);
            $cmd->bindValue(':in_receive_user_id', $receiveUserId);
            $cmd->bindValue(':in_integral', $integral);

            $status = $cmd->queryScalar();//存储过程处理成功后返回0

            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            $status = 1;
        }

        return $status;
    }

    /**
     * 合并用户
     * @param $userId1
     * @param $userId2
     * @return false|null|string
     * @throws \Exception
     * @throws \yii\db\Exception
     */
    public static function merge($userId1, $userId2)
    {
        if ($userId1 == $userId2) {
            return $userId1;
        }

        $user1 = self::getUser($userId1);
        $user2 = self::getUser($userId2);
        if ($user1 != null && $user2 != null && $user1['institution_code'] == $user2['institution_code']) {
            $userId = null;

            $transaction = \Yii::$app->db->beginTransaction();
            try {
                $cmd = \Yii::$app->db->createCommand('CALL p_merge_user(:in_master_user_id, :in_slave_user_id)');
                $cmd->bindValue(':in_master_user_id', $userId1);
                $cmd->bindValue(':in_slave_user_id', $userId2);

                $userId = $cmd->queryScalar();  //返回主用户id

                $transaction->commit();
            } catch (\Exception $e) {
                $transaction->rollBack();
                $userId = null;
            }

            return $userId;
        } else {
            throw new \Exception('合并用户错误!');
        }
    }

    /**
     * 获取不在部门的用户
     * @param $department_id
     * @param $institution_code
     * @return array
     */
    public static function getUserNotInDepartment($department_id, $institution_code)
    {
        $sql = 'SELECT DISTINCT t0.id,t0.name,t0.phone FROM t_user t0
                  LEFT JOIN t_department_user t1 ON t1.user_id = t0.id
                WHERE NOT exists(select 1 from t_department_user where t_department_user.user_id = t0.id AND department_id = :department_id) 
                AND t0.institution_code = :institution_code
                AND t0.is_enabled = 1';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);
        $cmd->bindValue(':department_id', $department_id);
        $items = $cmd->queryAll();
        return $items;
    }

    /**
     * 获取在部门的用户
     * @param $department_id
     * @param $institution_code
     * @return array
     */
    public static function getUserInDepartment($department_id, $institution_code)
    {
        $sql = 'SELECT t0.id,t0.name,t0.phone,t1.department_id FROM t_user t0
                  LEFT JOIN t_department_user t1 ON t1.user_id = t0.id
                WHERE t0.institution_code = :institution_code AND t1.department_id = :department_id';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);
        $cmd->bindValue(':department_id', $department_id);
        $items = $cmd->queryAll();
        return $items;
    }

    /**
     * 根据user_id获取部门名称(以，隔开)
     * @param $user_id
     * @return false|null|string
     */
    public static function getDepartmentByUserId($user_id)
    {
        $sql = 'SELECT Group_concat(t0.name) FROM t_department t0 INNER JOIN t_department_user t1 ON t1.department_id = t0.id
                WHERE t1.user_id = :user_id and t0.is_delete=0';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':user_id', $user_id);
        $result = $cmd->queryScalar();
        if (!$result) {
            return '';
        }
        return $result;
    }

    /**
     * 获取单个用户的积分记录
     * @param $start
     * @param $pageSize
     * @param $startTime
     * @param $endTime
     * @param $user_id
     * @return array|false
     */
    public static function getUserIntegralRecord($start, $pageSize, $startTime, $endTime, $user_id)
    {
        $filterSQL = 'user_id= :user_id';

        if ($startTime != '0') {
            $filterSQL = $filterSQL . ' and (create_datetime>= :startTime)';
        }
        if ($endTime != '0') {
            $filterSQL = $filterSQL . ' and (SUBSTRING(create_datetime FROM 1 FOR 10)<= :endTime)';
        }
        if ($pageSize == '-1') {
            $sql = "select count(*) as totalnum from t_user_integral_record where $filterSQL";
        } else {
            $sql = "select * from t_user_integral_record where $filterSQL order by create_datetime desc  LIMIT $start,$pageSize ";
        }


        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':user_id', $user_id);

        if ($startTime != '0') {
            $cmd->bindValue(':startTime', $startTime);
        }
        if ($endTime != '0') {
            $cmd->bindValue(':endTime', $endTime);
        }
        if ($pageSize == '-1') {
            $result = $cmd->queryOne();
            return $result;
        } else {
            $record = $cmd->queryAll();
            $result = [];
            foreach ($record as $item) {
                $typeAndRemark = MallFrontEnd::caseUserIntegralRecordTypeAndRemark($item['record_type'], $item['origin_id']);
                $result[] = [
                    'type' => $typeAndRemark[0],
                    'remark' => $typeAndRemark[1],
                    'integral' => floor($item['integral']),
                    'time' => $item['create_datetime'],
                ];
            }
            return $result;
        }
    }
}